<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class mdl_konversi_nilai extends CI_Model {
    
	function __construct(){
        parent::__construct();
    }
	
	function getdata($plimit=true){
		# get parameter from easy grid
		$page = isset($_POST['page']) ? intval($_POST['page']) : 1;  
		$limit = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
		$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'a.nim';  
		$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';  
		$offset = ($page-1)*$limit;
		
		#get filter
		$kd_fakultas = isset($_POST['kd_fakultas']) ? strval($_POST['kd_fakultas']) : '';
		$kd_prodi = isset($_POST['kd_prodi']) ? strval($_POST['kd_prodi']) : '';
		$tahun = isset($_POST['tahun']) ? strval($_POST['tahun']) : '';
		
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.id_krs, a.nim, b.nama, c.nama_dosen, a.ips, a.semester, sum(e.sks) as total_sks, count(e.kd_matakuliah) as total_mk');
			$this->db->from('tbl_krs a');
			$this->db->join('tbl_mahasiswa b', 'b.nim = a.nim');
			$this->db->join('ref_dosen c', 'c.kd_dosen = a.kd_dosen','left');
			$this->db->join('tbl_krs_detail d', 'd.id_krs = a.id_krs','left');
			$this->db->join('ref_matakuliah e', 'e.kd_matakuliah = d.kd_matakuliah','left');
			
			$this->db->where('a.kd_fakultas', $kd_fakultas);
			$this->db->where('a.kd_prodi', $kd_prodi);
			$this->db->where('a.id_tahunajaran', $tahun);
			
			$this->db->group_by('id_krs');
			
			$this->db->order_by($sort, $order);
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data
		if($plimit == true){
			$this->db->limit($limit, $offset);
		}
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
	}
	
	function getdata_detail($id){
		$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'nama_matakuliah';  
		$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';  
		
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.kd_matakuliah, b.nama_matakuliah, b.sks, b.semester');
			$this->db->from('tbl_krs_detail a');
			$this->db->join('ref_matakuliah b', 'b.kd_matakuliah = a.kd_matakuliah');
			$this->db->where('a.id_krs', $id);
			$this->db->order_by($sort, $order);
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
	}
	
	function togrid($data, $count){
		$response->total = $count;
		$response->rows = array();
		if($count>0){
			$i=0;
			foreach($data->result_array() as $row){
				foreach($row as $key => $value){
					$response->rows[$i][$key]=$value;
				}
				$i++;
			}
		}
		return json_encode($response);
	}
	
	function isExist_krs($data){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('tbl_krs a');
		
		$this->db->where('a.kd_fakultas', $data['kd_fakultas']);
		$this->db->where('a.kd_prodi', $data['kd_prodi']);
		$this->db->where('a.nim', $data['nim']);
		//$this->db->where('a.id_tahunajaran', $data['id_tahunajaran']);
		
		$r = $this->db->get();
		if($r->num_rows() > 0){
			return TRUE;
		}else{
			return FALSE;
		}
	}
	
	function getmatakuliah($kd_kurikulum, $kd_prodi){
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			//$this->db->select('kd_matakuliah, nama_matakuliah, sks, semester, kd_jenismk, space(0) as id_jadwal, space(0) as nama_dosen');
			$this->db->select("a.kd_matakuliah, a.nama_matakuliah, a.sks, a.semester, a.kd_jenismk, (select kd_bobot from ref_bobot) as kd_bobot, concat(b.kd_kelas, ' - ', c.nama_dosen) as nama_dosen, '0' as chk", false);
			$this->db->from('ref_matakuliah a');
			$this->db->join('tbl_jadwal b',"a.kd_matakuliah=b.kd_matakuliah and b.id_tahunajaran='$id_tahunajaran'",'left');
			$this->db->join('ref_dosen c','b.kd_dosen=c.kd_dosen','left');
			$this->db->where('a.kd_kurikulum', $kd_kurikulum);
			$this->db->where('a.kd_prodi', $kd_prodi);
			
			$this->db->group_by('a.kd_matakuliah');
			
			$this->db->order_by('a.semester' , 'ASC');
			$this->db->order_by('a.kd_matakuliah' , 'ASC');
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data		
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
		
	}
	
	function getsemester_mhs($nim){
		$this->db->flush_cache();
		$this->db->select('semester_mhsw');
		$this->db->where('nim', $nim);
		$this->db->where('semester !=', 'SP');
		$this->db->from('tbl_krs');
		$this->db->order_by('semester_mhsw', 'DESC');
		
		$r = $this->db->get();
		
		if($r->num_rows() > 0){
			return $r->row()->semester_mhsw + 1;
		}else{
			return 1;
		}
	}
	
	function getnomor(){
		$this->db->flush_cache();
		$query1 = $this->db->query("SHOW TABLE STATUS LIKE 'tbl_krs'");
		return $query1->row()->Auto_increment;
	}
	
	function InsertOnDb($data){
		$this->db->trans_start();
		
		# insert to krs
		$this->db->flush_cache();
        $this->db->set('kd_fakultas', $data['kd_fakultas']);
        $this->db->set('kd_prodi', $data['kd_prodi']);
        $this->db->set('nim', $data['nim']);
		$this->db->set('kd_dosen', $data['kd_dosen']);
        //$this->db->set('jum_sksk', $data['jum_sksk']);
        $this->db->set('ipk', '0');
        //$this->db->set('jum_skss', $data['sks']);
        //$this->db->set('ips', $data['ips']);
        $this->db->set('CUID', $this->session->userdata('user_id'));
        $this->db->set('CDATE', date('Y-m-d H:i:s'));
        $this->db->set('status', 'Approve');
		
		$this->db->insert('tbl_krs');
		
		#konversi Nilai
		$this->db->flush_cache();
				//$this->db->set('nim', $data['nim']);
				$this->db->set('verifikasi', 'Verifikasi');
				$this->db->where('nim', $data['nim']);
				$this->db->update('tbl_mutasi_mahasiswa');
				
		#insert Nilai Hasil Verifikasi
		foreach($data['data']['rows'] as $row){
			if($row['chk'] == '1'){
				$this->db->flush_cache();
				$this->db->set('id_krs', $data['nomor']);
				$this->db->set('kd_matakuliah', $row['kd_matakuliah']);
				$this->db->set('nilai_huruf', $row['kd_bobot']);
				$this->db->set('CUID', $this->session->userdata('user_id'));
				$this->db->set('CDATE', date('Y-m-d H:i:s'));
				
				$this->db->insert('tbl_krs_detail');
			}
		}
		
		$this->db->trans_complete();
	    return $this->db->trans_status();
	}
	
	function load_kd_bobot(){
		#get filter
		//$kd_fakultas = isset($_POST['kd_fakultas']) ? strval($_POST['kd_fakultas']) : '';
		
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('*');
			$this->db->from('ref_bobot');
			//$this->db->join('ref_dosen b', 'b.kd_dosen = a.kd_dosen');
			
			//$this->db->where('a.id_tahunajaran', $id_tahun_ajaran);
			//$this->db->where('a.kd_prodi', $kd_prodi);
			//$this->db->where('a.kd_matakuliah', $kd_matakuliah);
			
			$this->db->order_by('kd_bobot', 'ASC');
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
	}
	
	/* ------------ KPRS --------------------------*/
	
	function getdata_krs($plimit=true){
		#get filter
		$kd_fakultas = isset($_POST['kd_fakultas']) ? strval($_POST['kd_fakultas']) : '';
		$kd_prodi = isset($_POST['kd_prodi']) ? strval($_POST['kd_prodi']) : '';
		$id_tahunajaran = isset($_POST['id_tahunajaran']) ? strval($_POST['id_tahunajaran']) : '';
		$nim = isset($_POST['nim']) ? strval($_POST['nim']) : '';
		
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.id_krs, b.id_krs_detail, b.kd_matakuliah, c.nama_matakuliah, c.sks, c.semester, c.kd_jenismk');
			$this->db->from('tbl_krs a');
			$this->db->join('tbl_krs_detail b', 'b.id_krs = a.id_krs');
			$this->db->join('ref_matakuliah c', 'b.kd_matakuliah = c.kd_matakuliah');
			
			$this->db->where('a.kd_fakultas', $kd_fakultas);
			$this->db->where('a.kd_prodi', $kd_prodi);
			$this->db->where('a.id_tahunajaran', $id_tahunajaran);
			$this->db->where('a.nim', $nim);
			$this->db->where('b.status', 'A');
			
			//$this->db->order_by($sort, $order);
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
	}
	
	function delete_krs_detail($id){
		//query delet data  dengan ganti status db            
		$this->db->flush_cache();
		
		$this->db->set('status', "D");
	   
		$this->db->set('MUID', $this->session->userdata('user_id'));
		$this->db->set('MDATE', date('Y-m-d H:i:s'));
		
		$this->db->where('id_krs_detail', $id);
		$result = $this->db->update('tbl_krs_detail');
	   
		//return
		if($result) {
				return TRUE;
		}else {
				return FALSE;
		}
	}
	
	function isExist_detailkrs($data, &$id_krs_detail){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('tbl_krs a');
		$this->db->join('tbl_krs_detail b', 'b.id_krs = a.id_krs');
		
		$this->db->where('a.id_krs', $data['id_krs']);
		$this->db->where('a.kd_fakultas', $data['kd_fakultas']);
		$this->db->where('a.kd_prodi', $data['kd_prodi']);
		$this->db->where('a.nim', $data['nim']);
		$this->db->where('a.id_tahunajaran', $data['id_tahunajaran']);
		$this->db->where('b.kd_matakuliah', $data['kd_matakuliah']);
		
		$r = $this->db->get();
		if($r->num_rows() > 0){
			$id_krs_detail = $r->row()->id_krs_detail;
			return TRUE;
		}else{
			return FALSE;
		}
	}
	
	function update_status_detailkrs($id_krs_detail, $status){
		$this->db->flush_cache();
		$this->db->set('status', $status);
		$this->db->where('id_krs_detail', $id_krs_detail);
		return $this->db->update('tbl_krs_detail');
	}
	
	function insert_detailkrs($data){
		$this->db->flush_cache();
		$this->db->set('id_krs', $data['id_krs']);
		$this->db->set('kd_matakuliah', $data['kd_matakuliah']);
		$this->db->set('id_jadwal', $data['id_jadwal']);
		$this->db->set('CUID', $this->session->userdata('user_id'));
		$this->db->set('CDATE', date('Y-m-d H:i:s'));
		
		return $this->db->insert('tbl_krs_detail');
	}
	
}

?>